<?php
/**
 * 基础资料模块-产品列表
 */
class Base_ProductListController extends Controller_NeedLogin {

	public function init() {
		parent::init();
		$this->db = Model_Db::getInstance();
	}

	/**
	 * 产品列表
	 * @return [type] [description]
	 */
	public function indexAction() {
		$productGradeModel = new Application_Model_ProductGrade();
		$productGrade = $productGradeModel->getName();
		$goodsResourceStatusName = Application_Model_Product::RESOURCE_STATUS;
		$goodsCheckStatusName = Application_Model_Product::getCheckStatusName();
		$goodsStatusName = Application_Model_Product::getStatusName();

		$pageLink = "";
		$searchProductResult = $this->_search();
		if (!empty($searchProductResult)) {
			$pageLink = $this->page($searchProductResult['allCountNumber'], $this->page_size);
			$searchProductResult = $searchProductResult['list'];
		}

		$queryParameter = $this->getRequest()->getQuery();
		//获取分类的父级信息，用于设置查询页面类别筛选条件选择框
		$queryParameter['catalogPath'] = '';
		$queryParameter['edit_status'] = $queryParameter['edit_status'] ?? 0;
		if (isset($queryParameter['catalog_id']) && $queryParameter['catalog_id']) {
			$ProductCatalogModel = new Application_Model_ProductCatalog();
			$catalogPath = $ProductCatalogModel->getDetailById($queryParameter['catalog_id'], ['path']);
			$queryParameter['catalogPath'] = $catalogPath['path'] ?? "";
			$queryParameter['catalogPath'] = ltrim($queryParameter['catalogPath'], "0,");
		}

		$defaultTableFiled = 'pcode,pthumb,title,product_from,develop_category,develop_website,apply_user,develop_user,develop_mark,develop_status,promote_time,promote_mark,promote_status,link_id,post,promote_time_limit,sale,click,data2,like,share,feedback_time';
		$productTableFiled = isset($_COOKIE['productTableFiled_productlist']) && !empty($_COOKIE['productTableFiled_productlist']) ? $_COOKIE['productTableFiled_productlist'] : $defaultTableFiled;
		$productTableFiled = array_filter(explode(',', $productTableFiled));
		$this->smarty->assign('productTableFiled', $productTableFiled);

		$this->smarty->assign('pageLink', $pageLink);
		$this->smarty->assign('queryParameter', $queryParameter);
		$this->smarty->assign('productGrade', $productGrade);
		$this->smarty->assign('goodsResourceStatusName', $goodsResourceStatusName);
		$this->smarty->assign('goodsCheckStatusName', $goodsCheckStatusName);
		$this->smarty->assign('goodsStatusName', $goodsStatusName);
		$this->smarty->assign('searchProductResult', $searchProductResult);
		$this->smarty->assign('pdmDomain', Helper_Config::get('url', 'pdm', 'url'));
		$this->smarty->display('productList.tpl');
	}

	/**
	 * 执行产品搜索
	 * @return [type] [description]
	 */
	protected function _search() {
		$requestParameter = $this->getRequest()->getQuery();
		$searchParameter = $this->_productSearchParameterParser($requestParameter);
		if (!$searchParameter) {
			return [];
		}

		/**
		 * 产品的筛选，其实就是以产品相关的几个表中某些字段为搜索条件，来搜索符合条件的产品
		 * 所以我们根据请求参数，分别找到筛选参数属于的表和字段，然后拼接成sql语句
		 */
		$tableQuerySqlFormat = "select %s from p_product as product
		     left join p_product_purchase as purchase on product.goods_sn=purchase.goods_sn
		     left join p_product_purchase_info as purchase_info on product.goods_sn=purchase_info.goods_sn
		     left join p_product_supplier as supplier on purchase.supplier_sn=supplier.sp_code ";

		$whereCondition = "";
		$whereCondition .= $this->_productSearchCondition($searchParameter);
		$whereCondition .= $this->_productPurchaseSearchCondition($searchParameter);
		$whereCondition .= $this->_productPurchaseInfoSearchCondition($searchParameter);
		$whereCondition .= $this->_productSupplierSearchCondition($searchParameter);
		$whereCondition .= " AND product.goods_sn !=''"; //有goods_sn的脏数据
		$whereCondition = ltrim($whereCondition, "AND");
		$whereCondition = $whereCondition ? " where " . $whereCondition : "";

		$queryAllCountNumberSql = sprintf($tableQuerySqlFormat, 'count(*)') . $whereCondition;
		$data['allCountNumber'] = $this->db->fetchOne($queryAllCountNumberSql);
		$queryListSql = sprintf($tableQuerySqlFormat, 'product.*,purchase.*,purchase_info.*,supplier.sp_code,supplier.sp_name,product.*') . $whereCondition . " order by product.goods_sn";

		$params['page'] = $this->getRequest()->getParam('page') ?? 1;
		$params['perPage'] = $this->page_size;

		$queryListSql .= " limit " . ($params['page'] - 1) * $params['perPage'] . "," . $params['perPage'];
		$searchProductResult = $this->db->query($queryListSql)->fetchAll();

		if (!empty($searchProductResult)) {
			/**
			 * 因为想避免多表联查，所有产品列表的一些属性采取先获取搜索结果，然后根据搜索结果产品的属性外键
			 * 去获取属性的详细信息
			 */
			$searchProductResultGoodsSn = array_column($searchProductResult, 'goods_sn');

			//获取产品的分类名称
			$searchProductResultCatalogId = array_column($searchProductResult, 'catalog_id');
			$productCatalogModel = new Application_Model_ProductCatalog();
			$searchProductResultCatalogFullName = $productCatalogModel->getCatalogFullName($searchProductResultCatalogId);

			//获取产品的销售仓库
			$searchProductResultWhCode = array_column($searchProductResult, 'wh_code');
			$searchProductResultWhCode = array_unique($searchProductResultWhCode);
			$goodsWarehouse = [];
			if ($searchProductResultWhCode) {
				$warehouseModel = new Application_Model_Warehouse();
				$goodsWarehouse = $warehouseModel->getWarehouseByCode($searchProductResultWhCode);
				$goodsWarehouse = array_column($goodsWarehouse, 'warehouse_name', 'warehouse_code');
			}

			$moduleProductModel = new Base_Model_Product();
			//获取产品的新品仓库
			$goodsIsNewWarehouse = $moduleProductModel->getGoodsNewWarehouse($searchProductResultGoodsSn);

			//获取产品已经清仓的仓库名称
			$goodsIsClearanceWarehouse = $moduleProductModel->getGoodsEmptyWarehouse($searchProductResultGoodsSn);

			foreach ($searchProductResult as &$oneSearchedProduct) {
				$catalogFullName = $searchProductResultCatalogFullName[$oneSearchedProduct['catalog_id']];
				$catalogFullName_arr = explode("|", $catalogFullName);
				$oneSearchedProduct['catalogName'] = $catalogFullName_arr[0] ?? "";
				//清仓仓库
				$oneSearchedProduct['productEmptyWarehouse'] = $goodsIsClearanceWarehouse[$oneSearchedProduct['goods_sn']] ?? [];
				$oneSearchedProduct['productEmptyWarehouse'] = implode(" ", $oneSearchedProduct['productEmptyWarehouse']);
				//新品仓库
				$oneSearchedProduct['productIsNewWarehouse'] = $goodsIsNewWarehouse[$oneSearchedProduct['goods_sn']] ?? [];
				$oneSearchedProduct['productIsNewWarehouse'] = implode(" ", $oneSearchedProduct['productIsNewWarehouse']);

				$oneSearchedProduct['sales_warehouse'] = $goodsWarehouse[$oneSearchedProduct['wh_code']] ?? "";
			}
		}
		$data['list'] = $searchProductResult;
		return $data;
	}

	/**
	 * 产品列表搜索参数解析
	 * @param  [type] $searchParameter 搜索请求参数
	 * @return array
	 */
	protected function _productSearchParameterParser(array $searchParameter) {
		if (empty($searchParameter)) {
			return $searchParameter;
		}

		//过滤掉筛选参数值是空值和0的筛选项
		$searchParameter = array_filter($searchParameter, function ($searchVal) {
			$searchVal = trim($searchVal);
			if (!$searchVal) {
				return false;
			}
			return true;
		});

		//去除参数多余的空格
		$searchParameter = array_map(function ($item) {
			if (is_string($item)) {
				return trim($item);
			}
			return $item;
		}, $searchParameter);

		//字符关键词搜索
		if (@$searchParameter['character_search_select_value'] && @$searchParameter['character_search_select']) {
			$searchParameter[$searchParameter['character_search_select']] = $searchParameter['character_search_select_value'];
		}
		unset($searchParameter['character_search_select_value'], $searchParameter['character_search_select']);

		/**
		 * 将价格|数量|时间等区间型搜索条件转换成['搜索字段'=>['搜索区间最小值','搜索区间最大值']格式
		 */
		$rangSearchType = ['price', 'number', 'time'];
		foreach ($rangSearchType as $_searchType) {
			$searchTypeFormFieldName = $_searchType . "_range_search_type";
			$searchTypeMinValueFormFieldName = $_searchType . '_range_search_min_value';
			$searchTypeMaxValueFormFieldName = $_searchType . '_range_search_max_value';
			if (!@$searchParameter[$searchTypeFormFieldName]) {
				continue;
			}
			//搜索字段名
			$searchField = $searchParameter[$searchTypeFormFieldName];
			//搜索字值区间
			$searchTypeValueRange = [];

			if (@$searchParameter[$searchTypeMinValueFormFieldName]) {
				$searchTypeValueRange[] = $searchParameter[$searchTypeMinValueFormFieldName];
			}
			if (@$searchParameter[$searchTypeMaxValueFormFieldName]) {
				$searchTypeValueRange[1] = $searchParameter[$searchTypeMaxValueFormFieldName];
			}
			if (!empty($searchTypeValueRange)) {
				$searchParameter[$searchField] = $searchTypeValueRange;
			}
			unset($searchParameter[$searchTypeFormFieldName], $searchParameter[$searchTypeMinValueFormFieldName], $searchParameter[$searchTypeMaxValueFormFieldName]);
		}
		unset($_searchType);

		return $searchParameter;
	}

	/**
	 * 获取产品列表搜索-产品表字段筛选的sql
	 * @param  [type] $searchParameter    用户提交的所有的产品筛选条件
	 * @param  string $productTableAsName 产品表在筛选查询语句中的别名
	 * @return 如果搜索条件有使用product表，返回sql,否则返回空
	 */
	protected function _productSearchCondition($searchParameter, $productTableAsName = "product") {
		//产品表中支持确值查询(field=xxx)的字段
		$supportPreciseValueSearchField = ['researcher' /*调研员*/, 'update_user' /*更新人*/, 'product_grade' /*产品级别*/, 'recommend_level' /*推荐级别*/, 'product_status' /*产品状态*/, 'wh_code' /*销售仓库*/];
		//产品表中支持区间查询(field>xxx and field < xxx)的字段
		$supportRangeSearchField = ['sale_price_base' /*销售价格*/, 'shelf_time' /*上架时间*/];

		$whereConditionSql = "";

		//产品分类字段筛选sql语句
		if (isset($searchParameter['catalog_id']) && $searchParameter['catalog_id']) {
			$productCatalogModel = new Application_Model_ProductCatalog();
			$catalogChildCatalog = $productCatalogModel->getAllChildCatalog((int) $searchParameter['catalog_id'], ['id']);
			if ($catalogChildCatalog) {
				$catalogChildCatalogId = array_column($catalogChildCatalog, 'id');
				$whereConditionSql .= "AND " . $this->db->quoteInto("$productTableAsName.catalog_id in(?)", $catalogChildCatalogId);
			}
		}

		//拼接形成产品审核状态字段筛选sql语句
		if (isset($searchParameter['edit_status'])) {
			$editStatusArr = explode(',', $searchParameter['edit_status']);
			$editStatusArr = array_map('intval', $editStatusArr);
			$whereConditionSql .= 'AND ' . $this->db->quoteInto("$productTableAsName.edit_status in(?)", $editStatusArr);
		}
		// 是否有sku
		if (isset($searchParameter['goods_sn'])) {
			$goods_sn_arr = explode(',', $searchParameter['goods_sn']);
			$whereConditionSql .= 'AND ' . $this->db->quoteInto("$productTableAsName.goods_sn in(?)", $goods_sn_arr);
		}
		$whereConditionSql .= $this->_getRangeSearchSql($searchParameter, $productTableAsName, $supportRangeSearchField);
		$whereConditionSql .= $this->_getPreciseValueSearchSql($searchParameter, $productTableAsName, $supportPreciseValueSearchField);
		return $whereConditionSql;
	}

	/**
	 * 获取产品列表搜索-备货表字段筛选的sql
	 * @param  [type] $searchParameter    用户提交的所有的产品筛选条件
	 * @param  string $productTableAsName product_stockin表在筛选查询语句中的别名
	 * @return 如果搜索条件有使用product_stocking表，返回sql,否则返回空
	 */
	protected function _productPurchaseInfoSearchCondition($searchParameter, $productStockTableAsName = "purchase_info") {
		$supportRangeSearchField = ['package_num' /*回货装箱数*/, 'min_order' /*最小订货量*/, 'agent_inventory' /*代理商库存*/];
		return $this->_getRangeSearchSql($searchParameter, $productStockTableAsName, $supportRangeSearchField);
	}

	/**
	 *  获取产品列表搜索-采购表字段筛选的sql
	 * @param  [type] $searchParameter    用户提交的所有的产品筛选条件
	 * @param  string $productTableAsName product_purchase表在筛选查询语句中的别名
	 * @return 如果搜索条件有使用采购表，返回sql,否则返回空
	 */
	protected function _productPurchaseSearchCondition($searchParameter, $productPurchaseTableAsName = "purchase") {
		$supportPreciseValueSearchField = ['purchaser' /*采购员*/, 'goods_resource_status' /*货源状态*/];
		$supportRangeSearchField = ['purchase_price' /*采购价*/, 'purchase_price_batch' /*批量采购价*/, 'last_storage_time' /*最后入库时间*/];

		$whereConditionSql = "";
		$whereConditionSql .= $this->_getRangeSearchSql($searchParameter, $productPurchaseTableAsName, $supportRangeSearchField);
		$whereConditionSql .= $this->_getPreciseValueSearchSql($searchParameter, $productPurchaseTableAsName, $supportPreciseValueSearchField);
		return $whereConditionSql;
	}

	/**
	 * 获取产品列表搜索中以供应商表字段筛选的sql
	 * @param  [type] $searchParameter    用户提交的所有的产品筛选条件
	 * @param  string $productTableAsName product_supplier表在筛选查询语句中的别名
	 * @return 如果搜索条件有使用供应商表，返回sql,否则返回空
	 */
	protected function _productSupplierSearchCondition($searchParameter, $productSupplierTableAsName = "supplier") {
		$supportPreciseValueSearchField = ['sp_code' /*供应商编号*/, 'sp_name' /*供应商名称*/];
		return $this->_getPreciseValueSearchSql($searchParameter, $productSupplierTableAsName, $supportPreciseValueSearchField);

	}

	/**
	 * 获取确值条件类型(table.field="xxxx")拼接后的sql语句
	 * @param  array $searchParameter          用户提交的全部产品筛选条件
	 * @param  [type] $tableName               筛选字段的表名
	 * @param  [type] $tableSupportSearchField 表支持的确值筛选的字段名
	 * @return string
	 */
	protected function _getPreciseValueSearchSql($searchParameter, $tableName, $tableSupportPreciseSearchField) {
		$searchUseFields = array_intersect($tableSupportPreciseSearchField, array_keys($searchParameter));

		if (!$searchUseFields) {
			return "";
		}
		$sql = "";
		foreach ($searchUseFields as $useField) {
			$sql .= "AND " . $tableName . '.' . $useField . " = " . $this->db->quote($searchParameter[$useField]) . " ";
		}
		return $sql;
	}

	/**
	 * 获取区间条件类型(table.fied >xxx and table.fied< xxx)拼接后的sql
	 * @param  [type] $searchParameter              用户提交的全部产品筛选条件
	 * @param  [type] $tableName                    字段所属表名
	 * @param  [type] $tableSupportRangeSearchField 表支持的区间条件筛选的字段名
	 * @return string
	 */
	protected function _getRangeSearchSql($searchParameter, $tableName, $tableSupportRangeSearchField) {
		$searchUseFields = array_intersect($tableSupportRangeSearchField, array_keys($searchParameter));
		if (!$searchUseFields) {
			return "";
		}

		$RangeSearchSql = "";
		foreach ($searchUseFields as $useField) {
			$searchRange = $searchParameter[$useField];
			if (empty($searchRange)) {
				continue;
			}

			//最小值
			if (isset($searchRange[0])) {
				$fieldRangeSql = $tableName . '.' . $useField . " >= " . $this->db->quote($searchRange[0]);
			}

			//最大值
			if (isset($searchRange[1])) {
				$fieldRangeSql .= ($fieldRangeSql != "") ? " AND" : "";
				$fieldRangeSql .= " " . $tableName . '.' . $useField . " <= " . $this->db->quote($searchRange[1]);
			}

			$RangeSearchSql .= "AND ( " . $fieldRangeSql . " ) ";
		}
		return $RangeSearchSql;
	}
}